2.3 PostgreSQL explain

This is a historical learning note and may contain outdated or incomplete understanding.

1. Without WHERE

1.1. Ordinary explain

explain select * from foo;

Result

Seq Scan on foo  (cost=0.00..18918.18 rows=1058418 width=36)

1.1.1. Analysis

Read method
- Sequential scan, reading block by block.
Statistics
- cost
  - Time to obtain the first row.
  - Time to obtain all rows.
  - The unit is a planner cost unit, not milliseconds.
rows
- Number of rows scanned.
width
- Average length of all rows.
- Unit: bytes.
- 36 bytes because the UUID string occupies 32 bytes and the id int occupies 4 bytes.

1.2. analyze

analyze foo;
explain select * from foo;

Result

Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37)

1.2.1. Analysis

Using analyze means analyzing the foo table for subsequent planning.

rows
- It is indeed 1,000,000 rows now.
cost
- Slightly smaller than before.
width
- One byte larger than before.

1.3. How analyze Runs

analyze randomly reads part of the specified table and collects statistics. The amount of sampling is affected by the default_statistics_target parameter. The collected statistics are stored in the pg_statistic table. This table is difficult to read directly; pg_stats is generally used for analysis.

1.3.1. Examples

width

SELECT sum(avg_width) AS width
FROM pg_stats
WHERE tablename='foo';

//37

The width information is calculated by summing avg_width in pg_stats.

rows

SELECT reltuples FROM pg_class WHERE relname='foo';

//1000000

The rows information comes from reltuples in pg_class. pg_class is the catalog table for relations such as tables, indexes, and sequences, and stores their metadata.

cost

SELECT relpages*current_setting('seq_page_cost')::float4
+ reltuples*current_setting('cpu_tuple_cost')::float4
AS total_cost
FROM pg_class
WHERE relname='foo';

//18334

A PostgreSQL query needs to do two things:

Read all blocks of the table.
- Number of blocks (relpages) × cost per block (seq_page_cost in postgresql.conf).
Check whether each row satisfies the condition.
- Number of rows (reltuples) × per-row cost (cpu_tuple_cost).

1.4. How the Query Is Actually Executed

explain analyze SELECT * FROM foo;

Result

Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.034..295.673 rows=1000000 loops=1)
Planning Time: 0.088 ms
Execution Time: 419.848 ms

1.4.1. Analysis

The actual execution information is added.

actual time
- Actual time.
- Unit: milliseconds.
rows
- Actual number of rows read.
loops
- Number of loops.

2. Without WHERE, Increase the Buffer

2.1. Clear the Buffer and View Buffer Information

Clear the buffer and restart PostgreSQL.

3281  sudo systemctl stop postgresql.service
3284  sudo sync
3285  sudo echo 3 > /proc/sys/vm/drop_caches
3287  sudo systemctl start postgresql

EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo;

Result

Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=42.229..869.556 rows=1000000 loops=1)
  Buffers: shared read=8334
Planning Time: 182.176 ms
Execution Time: 999.686 ms

2.1.1. Analysis

After adding the BUFFERS option, buffer-related information appears.

Buffers: shared read=8334
- After the restart there is no cache, so 8334 blocks need to be read into PostgreSQL.

2.2. Execute Again After Cache Warm-Up

Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.149..396.740 rows=1000000 loops=1)
  Buffers: shared hit=32 read=8302
Planning Time: 0.084 ms
Execution Time: 566.195 ms

2.2.1. Analysis

This time 32 blocks are read from PostgreSQL’s cache, while the other 8302 blocks still need to be read from disk. There are two reasons why not everything is read from the buffer:

The main reason is that PostgreSQL’s cache mechanism uses ring-buffer optimization and does not load all data from a table into cache.
The secondary reason is that PostgreSQL’s buffer area is relatively small.

SELECT current_setting('shared_buffers') AS shared_buffers,
pg_size_pretty(pg_table_size('foo')) AS table_size;

//128MB,65 MB

2.3. Increase shared_buffers and Execute Again

/var/lib/postgres/data/postgresql.conf

shared_buffers=300MB

Restart

sudo systemctl restart postgresql

Execute again

Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.078..582.985 rows=1000000 loops=1)
  Buffers: shared read=8334
Planning Time: 1.452 ms
Execution Time: 767.469 ms

Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.042..320.293 rows=1000000 loops=1)
  Buffers: shared hit=8334
Planning Time: 0.110 ms
Execution Time: 486.366 ms

After increasing the cache, the time drops from 767.469 ms to 486.366 ms.

3. Add WHERE

3.1. explain After Using WHERE

explain select * from foo where c1 > 500;

Result

Seq Scan on foo  (cost=0.00..20834.00 rows=999575 width=37)
  Filter: (c1 > 500)

3.1.1. Analysis

cost
- It becomes larger because the condition needs to be checked.
rows
- The estimated number of returned rows becomes smaller.

3.2. How It Is Calculated

cost

SELECT
relpages*current_setting('seq_page_cost')::float4
+ reltuples*current_setting('cpu_tuple_cost')::float4
+ reltuples*current_setting('cpu_operator_cost')::float4
AS total_cost
FROM pg_class
WHERE relname='foo';

3.2.1. Analysis

All blocks need to be scanned.
Check the visibility of each row.
Apply an operation to a column of each row.
rows

SELECT histogram_bounds
FROM pg_stats
WHERE tablename='foo' AND attname='c1';

SELECT round(
(
(10590.0-500)/(10590-57)
+
(current_setting('default_statistics_target')::int4-1)
)
* 10000.0
) AS rows;

Result

Analysis Use the data obtained from the histogram. First divide all rows into 100 groups (specified by default_statistics_target), with 10,000 rows in each group.

3.3. Add an Index

CREATE INDEX ON foo(c1);
EXPLAIN SELECT * FROM foo WHERE c1 > 500;

Result

Seq Scan on foo  (cost=0.00..20834.00 rows=999556 width=37)
  Filter: (c1 > 500)

3.3.1. Analysis

The index is not used because there are 1,000,000 rows in total and only 500 rows are filtered out. Using CPU filtering directly is cheaper than using the index.

3.4. Force the Index to Be Used

Original result

Seq Scan on foo  (cost=0.00..20834.00 rows=999556 width=37) (actual time=0.255..636.487 rows=999500 loops=1)
  Filter: (c1 > 500)
  Rows Removed by Filter: 500
Planning Time: 0.279 ms
Execution Time: 811.279 ms

After using the index

SET enable_seqscan TO off;
EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c1 > 500;
SET enable_seqscan TO on;

Result

Index Scan using foo_c1_idx on foo  (cost=0.42..36802.65 rows=999556 width=37) (actual time=0.163..706.747 rows=999500 loops=1)
  Index Cond: (c1 > 500)
Planning Time: 0.204 ms
Execution Time: 818.279 ms

3.4.1. Analysis

706 > 636, so it is indeed slower.

3.5. Another Query

EXPLAIN SELECT * FROM foo WHERE c1 < 500;

Result

Index Scan using foo_c1_idx on foo  (cost=0.42..23.18 rows=443 width=37)
  Index Cond: (c1 < 500)

4. LIKE After WHERE

4.1. Use LIKE

EXPLAIN SELECT * FROM foo WHERE c1 < 500 and c2 LIKE 'abcd%';

Result

Index Scan using foo_c1_idx on foo  (cost=0.42..24.29 rows=1 width=37)
  Index Cond: (c1 < 500)
  Filter: (c2 ~~ 'abcd%'::text)

4.1.1. Analysis

The c1 index is used as Index Cond, while a Filter is also used.

4.2. LIKE Alone

EXPLAIN analyze
SELECT * FROM foo WHERE c2 LIKE 'abcd%';

Result

Gather  (cost=1000.00..14552.33 rows=100 width=37) (actual time=16.195..250.626 rows=22 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on foo  (cost=0.00..13542.33 rows=42 width=37) (actual time=44.509..232.877 rows=7 loops=3)
        Filter: (c2 ~~ 'abcd%'::text)
        Rows Removed by Filter: 333326
Planning Time: 0.139 ms
Execution Time: 250.692 ms

4.2.1. Analysis

Sequential scan. Each worker removes about 333,326 rows; the actual result returned by the whole query is 22 rows.

4.3. Add an Index

CREATE INDEX ON foo(c2);
EXPLAIN (ANALYZE) SELECT * FROM foo
WHERE c2 LIKE 'abcd%'

Result

Gather  (cost=1000.00..14552.33 rows=100 width=37) (actual time=28.965..296.905 rows=22 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on foo  (cost=0.00..13542.33 rows=42 width=37) (actual time=81.060..271.243 rows=7 loops=3)
        Filter: (c2 ~~ 'abcd%'::text)
        Rows Removed by Filter: 333326
Planning Time: 0.194 ms
Execution Time: 296.967 ms

4.3.1. Analysis

The index is not used because the c2 column is stored with UTF-8, while the default index operator class does not support this pattern-matching access path under this collation setup.

4.3.2. Solution

Force a specific index operator class.

CREATE INDEX ON foo(c2 text_pattern_ops);
EXPLAIN SELECT * FROM foo WHERE c2 LIKE 'abcd%';

Result

Index Scan using foo_c2_idx1 on foo  (cost=0.42..8.45 rows=100 width=37)
  Index Cond: ((c2 ~>=~ 'abcd'::text) AND (c2 ~<~ 'abce'::text))
  Filter: (c2 ~~ 'abcd%'::text)

5. Use a Covering Index

5.1. Query Using a Covering Index

EXPLAIN SELECT c1 FROM foo WHERE c1 < 500;

Result

Index Only Scan using foo_c1_idx on foo  (cost=0.42..23.18 rows=443 width=4)
  Index Cond: (c1 < 500)

5.1.1. Analysis

The fields after SELECT and WHERE are fields in the index, so Index Only Scan appears.

6. LIMIT

6.1. Without LIMIT

EXPLAIN (ANALYZE,BUFFERS)
SELECT * FROM foo WHERE c2 LIKE 'ab%';

Result

Gather  (cost=1000.00..15552.43 rows=10101 width=37) (actual time=0.917..232.718 rows=3927 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=8334
  ->  Parallel Seq Scan on foo  (cost=0.00..13542.33 rows=4209 width=37) (actual time=0.315..212.488 rows=1309 loops=3)
        Filter: (c2 ~~ 'ab%'::text)
        Rows Removed by Filter: 332024
        Buffers: shared hit=8334
Planning Time: 0.202 ms
Execution Time: 233.915 ms

6.2. With LIMIT

EXPLAIN (ANALYZE,BUFFERS)
SELECT * FROM foo WHERE c2 LIKE 'ab%' limit 10;

Result

Limit  (cost=0.00..20.63 rows=10 width=37) (actual time=0.148..0.761 rows=10 loops=1)
  Buffers: shared hit=21
  ->  Seq Scan on foo  (cost=0.00..20834.00 rows=10101 width=37) (actual time=0.145..0.754 rows=10 loops=1)
        Filter: (c2 ~~ 'ab%'::text)
        Rows Removed by Filter: 2496
        Buffers: shared hit=21
Planning Time: 0.175 ms
Execution Time: 0.796 ms

As shown above, Rows Removed by Filter: 2496 is much smaller.

7. HashJoin

7.1. Join Query Without Indexes

EXPLAIN (ANALYZE)
SELECT * FROM foo JOIN bar ON foo.c1=bar.c1;

Result

Hash Join  (cost=13463.00..40547.00 rows=500000 width=42) (actual time=564.550..2044.566 rows=500000 loops=1)
  Hash Cond: (foo.c1 = bar.c1)
  ->  Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.034..336.830 rows=1000000 loops=1)
  ->  Hash  (cost=7213.00..7213.00 rows=500000 width=5) (actual time=563.583..563.583 rows=500000 loops=1)
        Buckets: 524288  Batches: 1  Memory Usage: 22163kB
        ->  Seq Scan on bar  (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.054..213.412 rows=500000 loops=1)
Planning Time: 0.791 ms
Execution Time: 2132.985 ms

7.1.1. Analysis

There are many join methods. HashJoin is used for equality joins. First sequentially scan bar, calculate the hash value of each row, and store it in a hash table. Then sequentially scan foo, calculate the hash value of each row, and check whether it exists in bar’s hash table; if it does, join it. This type of join works well when memory is sufficient.

8. MergeJoin

Suitable for large tables.

8.1. Join Query After Adding an Index

CREATE INDEX ON bar(c1);
EXPLAIN (ANALYZE)
SELECT * FROM foo JOIN bar ON foo.c1=bar.c1;

Result

Merge Join  (cost=1.57..39912.55 rows=500000 width=42) (actual time=0.074..1174.615 rows=500000 loops=1)
  Merge Cond: (foo.c1 = bar.c1)
  ->  Index Scan using foo_c1_idx on foo  (cost=0.42..34317.43 rows=1000000 width=37) (actual time=0.035..287.165 rows=500001 loops=1)
  ->  Index Scan using bar_c1_idx on bar  (cost=0.42..15212.42 rows=500000 width=5) (actual time=0.026..326.117 rows=500000 loops=1)
Planning Time: 1.654 ms
Execution Time: 1230.054 ms

8.1.1. Analysis

If the join key is indexed (already sorted), MergeJoin is used.

8.2. LEFT JOIN with Sufficient Memory

EXPLAIN (ANALYZE)
SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;

Result

Hash Left Join  (cost=13463.00..40547.00 rows=1000000 width=42) (actual time=478.235..1674.451 rows=1000000 loops=1)
  Hash Cond: (foo.c1 = bar.c1)
  ->  Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.038..258.173 rows=1000000 loops=1)
  ->  Hash  (cost=7213.00..7213.00 rows=500000 width=5) (actual time=477.280..477.281 rows=500000 loops=1)
        Buckets: 524288  Batches: 1  Memory Usage: 22163kB
        ->  Seq Scan on bar  (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.054..177.797 rows=500000 loops=1)
Planning Time: 0.852 ms
Execution Time: 1776.768 ms

8.2.1. Analysis

The result is unexpectedly the same as when no index was created.

8.3. LEFT JOIN with Insufficient Memory

SET work_mem TO '1MB';

EXPLAIN (ANALYZE)
SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;

Result

Merge Left Join  (cost=1.57..58279.85 rows=1000000 width=42) (actual time=0.074..1556.423 rows=1000000 loops=1)
  Merge Cond: (foo.c1 = bar.c1)
  ->  Index Scan using foo_c1_idx on foo  (cost=0.42..34317.43 rows=1000000 width=37) (actual time=0.035..527.201 rows=1000000 loops=1)
  ->  Index Scan using bar_c1_idx on bar  (cost=0.42..15212.42 rows=500000 width=5) (actual time=0.026..277.211 rows=500000 loops=1)
Planning Time: 0.679 ms
Execution Time: 1663.584 ms

8.3.1. Analysis

This time Merge Join is used, and the time is 100 ms shorter.

8.4. Delete the Index and Query Most of the Data

DELETE FROM bar WHERE c1>500;
DROP INDEX bar_c1_idx;
ANALYZE bar;
EXPLAIN (ANALYZE)
SELECT * FROM foo JOIN bar ON foo.c1=bar.c1;

Result

Merge Join  (cost=2240.95..2264.69 rows=500 width=42) (actual time=66.961..68.302 rows=500 loops=1)
  Merge Cond: (foo.c1 = bar.c1)
  ->  Index Scan using foo_c1_idx on foo  (cost=0.42..34317.43 rows=1000000 width=37) (actual time=0.016..0.442 rows=501 loops=1)
  ->  Sort  (cost=2240.41..2241.66 rows=500 width=5) (actual time=66.928..67.058 rows=500 loops=1)
        Sort Key: bar.c1
        Sort Method: quicksort  Memory: 48kB
        ->  Seq Scan on bar  (cost=0.00..2218.00 rows=500 width=5) (actual time=0.026..66.804 rows=500 loops=1)
Planning Time: 0.492 ms
Execution Time: 68.443 ms

8.4.1. Analysis

First quicksort the bar table, then use Merge Join.

9. NestedLoop

Suitable for small tables.

9.1. After Deleting Most of the Data

DELETE FROM foo WHERE c1>1000;
ANALYZE foo;
EXPLAIN (ANALYZE)
SELECT * FROM foo JOIN bar ON foo.c1=bar.c1;

Result

Nested Loop  (cost=9.84..6939.13 rows=500 width=42) (actual time=0.073..6.593 rows=500 loops=1)
  ->  Seq Scan on bar  (cost=0.00..8.00 rows=500 width=5) (actual time=0.023..0.155 rows=500 loops=1)
  ->  Bitmap Heap Scan on foo  (cost=9.84..13.85 rows=1 width=37) (actual time=0.007..0.007 rows=1 loops=500)
        Recheck Cond: (c1 = bar.c1)
        Heap Blocks: exact=500
        ->  Bitmap Index Scan on foo_c1_idx  (cost=0.00..9.84 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=500)
              Index Cond: (c1 = bar.c1)
Planning Time: 0.996 ms
Execution Time: 6.785 ms

9.1.1. Analysis

Nested Loop is used. First sequentially scan the bar table.

9.2. After Truncating the Table

TRUNCATE bar;
ANALYZE bar;
EXPLAIN (ANALYZE)
SELECT * FROM foo JOIN bar ON foo.c1>bar.c1;

Result

Nested Loop  (cost=0.40..34657.73 rows=823333 width=42) (actual time=0.012..0.012 rows=0 loops=1)
  ->  Seq Scan on bar  (cost=0.00..34.70 rows=2470 width=5) (actual time=0.011..0.011 rows=0 loops=1)
  ->  Index Scan using foo_c1_idx on foo  (cost=0.40..10.69 rows=333 width=37) (never executed)
        Index Cond: (c1 > bar.c1)
Planning Time: 0.297 ms
Execution Time: 0.056 ms

9.2.1. Analysis

After truncating the table, PostgreSQL still estimates that there is data and still uses Nested Loop.

9.3. CROSS JOIN

EXPLAIN SELECT * FROM foo CROSS JOIN bar ;

Result

Nested Loop  (cost=0.00..30931.20 rows=2470000 width=42)
  ->  Seq Scan on bar  (cost=0.00..34.70 rows=2470 width=5)
  ->  Materialize  (cost=0.00..24.00 rows=1000 width=37)
        ->  Seq Scan on foo  (cost=0.00..19.00 rows=1000 width=37)

9.3.1. Analysis

Nested Loop is used.

10. ORDER BY

10.1. Query Using ORDER BY

DROP INDEX foo_c1_idx;
EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;

Result

Gather Merge  (cost=63789.50..161018.59 rows=833334 width=37) (actual time=531.824..1616.504 rows=1000000 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Sort  (cost=62789.48..63831.15 rows=416667 width=37) (actual time=521.651..786.417 rows=333333 loops=3)
        Sort Key: c1
        Sort Method: external merge  Disk: 12568kB
        Worker 0:  Sort Method: external merge  Disk: 16968kB
        Worker 1:  Sort Method: external merge  Disk: 16576kB
        ->  Parallel Seq Scan on foo  (cost=0.00..12500.67 rows=416667 width=37) (actual time=0.029..182.951 rows=333333 loops=3)
Planning Time: 0.447 ms
Execution Time: 1794.824 ms

10.1.1. Analysis

First sequentially scan the whole table, taking 182 ms. Then sort by field c1. The sorting method is external sort (because the space used, 12568 + 16968 + 16576 = 46M, is relatively large, so it is not sorted entirely in memory).

10.2. Determine Whether Disk Reads/Writes Really Occur

EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo ORDER BY c1;

Result

Gather Merge  (cost=63789.50..161018.59 rows=833334 width=37) (actual time=438.141..1391.189 rows=1000000 loops=1)
  Workers Planned: 2
  Workers Launched: 2
"  Buffers: shared hit=8428, temp read=5763 written=5786"
  ->  Sort  (cost=62789.48..63831.15 rows=416667 width=37) (actual time=429.588..646.041 rows=333333 loops=3)
        Sort Key: c1
        Sort Method: external merge  Disk: 19184kB
        Worker 0:  Sort Method: external merge  Disk: 14352kB
        Worker 1:  Sort Method: external merge  Disk: 12568kB
"        Buffers: shared hit=8428, temp read=5763 written=5786"
        ->  Parallel Seq Scan on foo  (cost=0.00..12500.67 rows=416667 width=37) (actual time=0.023..145.298 rows=333333 loops=3)
              Buffers: shared hit=8334
Planning Time: 0.134 ms
Execution Time: 1586.986 ms

10.2.1. Analysis

Buffers: shared hit=8428, temp read=5763 written=5786 Here 5763 temporary blocks were read and 5786 blocks were written. If each block is 8K, then the reads total 5763 * 8 ≈ 45M.

10.3. Try Using More Memory

SET work_mem TO '200MB';
EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;

Result

Sort  (cost=117991.84..120491.84 rows=1000000 width=37) (actual time=813.293..1026.148 rows=1000000 loops=1)
  Sort Key: c1
  Sort Method: quicksort  Memory: 102702kB
  ->  Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.040..356.703 rows=1000000 loops=1)
Planning Time: 0.166 ms
Execution Time: 1183.975 ms

10.3.1. Analysis

After increasing working memory to 200M, the query uses quicksort.

10.4. Create an Index on the Sorting Column

CREATE INDEX ON foo(c1);
EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;

Result

Index Scan using foo_c1_idx on foo  (cost=0.42..34317.43 rows=1000000 width=37) (actual time=0.096..566.817 rows=1000000 loops=1)
Planning Time: 0.554 ms
Execution Time: 682.179 ms

10.4.1. Analysis

Reading in index order is faster than quicksort in working memory, so PostgreSQL tends to use the index rather than in-memory quicksort.

10.5. Aggregation

10.6. count

EXPLAIN SELECT count(*) FROM foo;

Result

Aggregate  (cost=21.50..21.51 rows=1 width=8)
  ->  Seq Scan on foo  (cost=0.00..19.00 rows=1000 width=0)

10.6.1. Analysis

count uses a sequential scan.

10.7. max

DROP INDEX foo_c2_idx;
EXPLAIN (ANALYZE) SELECT max(c2) FROM foo;

Result

Aggregate  (cost=21.50..21.51 rows=1 width=32) (actual time=0.701..0.702 rows=1 loops=1)
  ->  Seq Scan on foo  (cost=0.00..19.00 rows=1000 width=33) (actual time=0.015..0.216 rows=1000 loops=1)
Planning Time: 0.319 ms
Execution Time: 0.747 ms

10.7.1. Analysis

Sequential scan is used to obtain the maximum value.

10.8. After Using an Index

CREATE INDEX ON foo(c2);
EXPLAIN (ANALYZE) SELECT max(c2) FROM foo;

Result

Result  (cost=0.33..0.34 rows=1 width=32) (actual time=0.146..0.146 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.28..0.33 rows=1 width=33) (actual time=0.134..0.136 rows=1 loops=1)
          ->  Index Only Scan Backward using foo_c2_idx on foo  (cost=0.28..57.77 rows=1000 width=33) (actual time=0.131..0.132 rows=1 loops=1)
                Index Cond: (c2 IS NOT NULL)
                Heap Fetches: 0
Planning Time: 0.674 ms
Execution Time: 0.200 ms

10.8.1. Analysis

Index Only Scan.

10.9. GROUP BY

DROP INDEX foo_c2_idx;
EXPLAIN (ANALYZE)
SELECT c2, count(*) FROM foo GROUP BY c2;

Result

HashAggregate  (cost=24.00..34.00 rows=1000 width=41) (actual time=1.022..1.529 rows=1000 loops=1)
  Group Key: c2
  ->  Seq Scan on foo  (cost=0.00..19.00 rows=1000 width=33) (actual time=0.019..0.226 rows=1000 loops=1)
Planning Time: 0.224 ms
Execution Time: 1.716 ms